package Dao;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.apache.commons.beanutils.BeanUtils;

import javax.sql.DataSource;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.*;

public class JdbcTools3 {

        public static Connection connection;
        public static DataSource dataSource;

        /**
         * 德鲁伊数据库连接池
         */
        static {
            try {
                InputStream inputStream = JdbcTools3.class.getClassLoader().getResourceAsStream("dridok.properties");
                Properties properties = new Properties();
                properties.load(inputStream);
                dataSource = DruidDataSourceFactory.createDataSource(properties);
                connection = dataSource.getConnection();
                System.out.println("my-connection = " + connection);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        /**
         * 2.获取完整的预编译好的sql对象
         *
         * @param sql 预编译的sql语句
         * @param connection db链接对象
         * @param parameters sql替换的参数
         * @return 预编译好的sql对象
         * @throws SQLException
         */
        public static PreparedStatement getPreparedStatement(String sql, Connection connection, Object... parameters) {
            PreparedStatement statement = null;
            try {
                statement = connection.prepareStatement(sql);
//1.获取元数据
                ParameterMetaData parameterMetaData = statement.getParameterMetaData();
//2.获取字符串需要的参数个数
                int count = parameterMetaData.getParameterCount();
//3.判断是否传参，并且设置参数值
                if (count != 0 && parameters != null && parameters.length == count)
                    for (int i = 0; i < count; i++) {
                        statement.setObject(i + 1, parameters[i]);
                    }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            return statement;
        }

        /**
         * 3.实现更新
         *
         * @param sql
         * @param parameters
         * @return
         * @throws SQLException
         */
        public static int update1(String sql, Object... parameters) {
            PreparedStatement preparedStatement = getPreparedStatement(sql, connection, parameters);
            int i = 0;
            try {
                i = preparedStatement.executeUpdate();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            return i;
        }

        /**
         * 4. 关闭对象，释放资源
         *
         * @param resources
         */
        public static void closeAll(AutoCloseable... resources) {
            if (resources != null && resources.length > 0) {
                Arrays.stream(resources).forEach(source -> {
                    if (source != null) {
                        System.out.println("source = " + source);
                        try {
                            source.close();
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }
                });
            }
        }


        /**
         * 5.根据条件查询一条数据,以传入的javaBean类型返回 null
         *
         * @param sql
         * @param cls
         * @param parameters
         * @param <T>
         * @return
         * @throws SQLException
         * @throws InstantiationException
         * @throws IllegalAccessException
         * @throws InvocationTargetException
         */
        public static <T> T queryBean(String sql, Class<T> cls, Object... parameters) {
//1.定义需要的变量
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            T t = null;//初始值
//2.获取链接
//3.获取sql的预编译对象
            preparedStatement = JdbcTools3.getPreparedStatement(sql, connection, parameters);
//4.执行查询，得到结果集
            try {
                resultSet = preparedStatement.executeQuery();
//5.反编译对象，从结果集中获取表的元数据
                ResultSetMetaData metaData = resultSet.getMetaData();
                System.out.println("metaData = " + metaData);

                if (resultSet.next())//false
                {
                    t = cls.newInstance();//实例化一个bean的空对象，用来封装数据
                    int count = metaData.getColumnCount();
                    for (int i = 1; i <= count; i++) {
                        BeanUtils.setProperty(t, metaData.getColumnName(i), resultSet.getObject(i));
                    }

// JdbcTools1.closeAll(connection, preparedStatement, resultSet);
                    return t;
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            } catch (InstantiationException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }
            return t;
        }

        /**
         * 6.根据条件查询多条数据 以集合的形式返回
         *
         * @param sql String
         * @param cls Class<T>
         * @param parameters 替换函数
         * @param <T>
         * @return
         * @throws SQLException
         * @throws InstantiationException
         * @throws IllegalAccessException
         * @throws InvocationTargetException
         */

        public static <T> List<T> queryBeanList(String sql, Class<T> cls, Object... parameters) {

//1.定义需要的变量
// Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;

            ArrayList<T> arrayList = new ArrayList<>();
            T t = null;
//2.获取链接

//3.获取sql的预编译对象
            preparedStatement = JdbcTools3.getPreparedStatement(sql, connection, parameters);
            try {
//4.执行查询，得到结果集
                resultSet = preparedStatement.executeQuery();
//5.反编译对象，从结果集中获取表的元数据
                ResultSetMetaData metaData = resultSet.getMetaData();
                System.out.println("metaData = " + metaData);
                int count = metaData.getColumnCount();

                while (resultSet.next()) {
                    t = cls.newInstance();//实例化一个bean的空对象，用来封装数据

                    for (int i = 1; i <= count; i++) {
                        BeanUtils.setProperty(t, metaData.getColumnName(i), resultSet.getObject(i));
                    }
                    arrayList.add(t);
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            } catch (InstantiationException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }
            JdbcTools3.closeAll(preparedStatement, resultSet);
            return arrayList;
        }

        /**
         * 7.查询多条记录，以Map集合的形式返回。
         *
         * @param sql
         * @param parameters
         * @return
         * @throws SQLException
         */
        public static ArrayList<Map> queryMapList(String sql, Object... parameters) {
//1.定义需要的变量
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;

            ArrayList<Map> maps = new ArrayList<>();
//2.获取链接
//3.获取sql的预编译对象
            preparedStatement = JdbcTools3.getPreparedStatement(sql, connection, parameters);
            try {
//4.执行查询，得到结果集
                resultSet = preparedStatement.executeQuery();
//5.反编译对象，从结果集中获取表的元数据
                ResultSetMetaData metaData = resultSet.getMetaData();
                int count = metaData.getColumnCount();

                while (resultSet.next()) {
                    HashMap<String, Object> map = new HashMap<>();
                    for (int i = 1; i <= count; i++) {
                        map.put(metaData.getColumnName(i), resultSet.getObject(i));
                    }
                    maps.add(map);
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

            JdbcTools3.closeAll(preparedStatement, resultSet);
            return maps;
        }

        /**
         * 8.查询一条记录，以数组形式返回
         *
         * @param sql
         * @param parameters
         * @return Array
         * @throws SQLException
         */
        public static Object[] queryArray(String sql, Object... parameters) {
//1.定义需要的变量
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
//2.获取链接
//3.获取sql的预编译对象
            preparedStatement = JdbcTools3.getPreparedStatement(sql, connection, parameters);
//4.执行查询，得到结果集
            Object[] o = new Object[0];//定义长度为count的空数组
            try {
                resultSet = preparedStatement.executeQuery();
//5.反编译对象，从结果集中获取表的元数据
                ResultSetMetaData metaData = resultSet.getMetaData();
                int count = metaData.getColumnCount();

                o = new Object[count];
                resultSet.next();
                for (int i = 0; i < count; i++) {
                    o[i] = resultSet.getObject(i + 1);
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
// JdbcTools1.closeAll(connection, preparedStatement, resultSet);
            return o;

        }


        /**
         * 9.查询多条记录，以数组集合（集合中的数据是数组）的数据状态返回
         *
         * @param sql sql查询语句
         * @param parameters 替换参数
         * @return List
         * @throws SQLException
         */
        public static List<Object[]> queryArrayList(String sql, Object... parameters) {
//1.定义需要的变量
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;

            List<Object[]> objects = new ArrayList<>();
//2.获取链接
//3.获取sql的预编译对象
            preparedStatement = JdbcTools3.getPreparedStatement(sql, connection, parameters);
            try {
//4.执行查询，得到结果集
                resultSet = preparedStatement.executeQuery();
//5.反编译对象，从结果集中获取表的元数据
                ResultSetMetaData metaData = resultSet.getMetaData();
                int count = metaData.getColumnCount();

                while (resultSet.next()) {
                    Object[] o = new Object[count];//定义长度为count的空数组
                    for (int i = 0; i < count; i++) {
                        o[i] = resultSet.getObject(i + 1);
                    }
                    objects.add(o);
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

// JdbcTools1.closeAll(connection, preparedStatement, resultSet);
            return objects;
        }


        /**
         * 10.查询一条记录，以键值对形式返回
         *
         * @param sql
         * @param parameters
         * @return
         * @throws SQLException
         */
        public static Map<String, Object> queryMap(String sql, Object... parameters) {
//1.定义需要的变量
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;

//2.获取链接
//3.获取sql的预编译对象
            preparedStatement = JdbcTools3.getPreparedStatement(sql, connection, parameters);
            HashMap<String, Object> map = null;
            try {
//4.执行查询，得到结果集
                resultSet = preparedStatement.executeQuery();
//5.反编译对象，从结果集中获取表的元数据
                ResultSetMetaData metaData = resultSet.getMetaData();
                int count = metaData.getColumnCount();
                map = new HashMap<>();
                while (resultSet.next()) {
                    for (int i = 1; i <= count; i++) {
                        map.put(metaData.getColumnName(i), resultSet.getObject(i));
                    }
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            JdbcTools3.closeAll(preparedStatement, resultSet);
            return map;
        }


        /**
         * 11.删除
         *
         * @param sql
         * @param parameters
         * @return
         * @throws SQLException
         */
        public static int del(String sql, Object... parameters) {
            PreparedStatement preparedStatement = getPreparedStatement(sql, connection, parameters);
            int i = 0;
            try {
                i = preparedStatement.executeUpdate();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            return i;
        }
}
